display_ads (view)

Data source: simonw/russian-ira-facebook-ads-datasette

8 rows where an ad_target was people_who_match:interests:Protect the Second Amendment

This data as JSON, CSV (advanced)

id	img	targeting	impressions	clicks	url	text	spend_usd	spend_amount	spend_currency	created	ended
2900		location_living_in:United States, exclude_location:united_states:California; Illinois, excluded_connections:Exclude people who like Defend the 2nd, age:18 - 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:The Second Amendment, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Anything About Guns, people_who_match:interests:Protecting Your Gun Rights, people_who_match:interests:God, people_who_match:interests:Guns, people_who_match:interests:& American Freedom, people_who_match:interests:Preserve our right to keep and bear arms, people_who_match:interests:Gun Rights, people_who_match:interests:Guns & Weapons or Second Amendment Supporters, people_who_match:interests:Politics: US politics (conservative), and_must_also_match:interests:Right to keep and bear arms, and_must_also_match:interests:AR-15, and_must_also_match:interests:2nd Amendment, and_must_also_match:interests:Gun Owners of America, and_must_also_match:interests:Guns & Ammo	0	0	https://www.facebook.com/savetheZa/	The community of 2nd Amendment supporters, guns lovers & patriots Defend The 2nd	0.0	0.00	USD	2017-04-19T07:31:12-07:00
2901		location_living_in:United States, exclude_location:united_states:California; Illinois, excluded_connections:Exclude people who like Defend the 2nd, age:18 - 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:The Second Amendment, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Anything About Guns, people_who_match:interests:Protecting Your Gun Rights, people_who_match:interests:God, people_who_match:interests:Guns, people_who_match:interests:& American Freedom, people_who_match:interests:Preserve our right to keep and bear arms, people_who_match:interests:Gun Rights, people_who_match:interests:Guns & Weapons or Second Amendment Supporters, people_who_match:interests:Politics: US politics (conservative), and_must_also_match:interests:AR-15, and_must_also_match:interests:2nd Amendment, and_must_also_match:interests:Gun Owners of America, and_must_also_match:interests:Guns & Ammo	0	0	https://www.facebook.com/savetheZa/	The community of 2nd Amendment supporters, guns lovers & patriots Defend The 2nd	0.0	0.00	USD	2017-04-19T07:32:00-07:00
2902		location_living_in:United States, exclude_location:united_states:California; Illinois, excluded_connections:Exclude people who like Defend the 2nd, age:18 - 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:The Second Amendment, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Anything About Guns, people_who_match:interests:Protecting Your Gun Rights, people_who_match:interests:God, people_who_match:interests:Guns, people_who_match:interests:& American Freedom, people_who_match:interests:Preserve our right to keep and bear arms, people_who_match:interests:Gun Rights, people_who_match:interests:Guns & Weapons or Second Amendment Supporters, people_who_match:interests:Politics: US politics (conservative), and_must_also_match:interests:AR-15, and_must_also_match:interests:2nd Amendment, and_must_also_match:interests:National Rifle Association, and_must_also_match:interests:Gun Owners of America	0	0	https://www.facebook.com/savetheZa/	The community of 2nd Amendment supporters, guns lovers & patriots Defend The 2nd	0.0	0.00	USD	2017-04-19T07:33:25-07:00
2903		location_living_in:United States, exclude_location:united_states:California; Illinois, excluded_connections:Exclude people who like Defend the 2nd, age:18 - 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:The Second Amendment, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Anything About Guns, people_who_match:interests:Protecting Your Gun Rights, people_who_match:interests:God, people_who_match:interests:Guns, people_who_match:interests:& American Freedom, people_who_match:interests:Preserve our right to keep and bear arms, people_who_match:interests:Gun Rights, people_who_match:interests:Guns & Weapons or Second Amendment Supporters, people_who_match:interests:Politics: US politics (conservative), and_must_also_match:interests:2nd Amendment, and_must_also_match:interests:National Rifle Association	0	0	https://www.facebook.com/savetheZa/	The community of 2nd Amendment supporters, guns lovers & patriots Defend The 2nd	0.0	0.00	USD	2017-04-19T07:35:22-07:00
2904		location_living_in:United States, exclude_location:united_states:California; Illinois, excluded_connections:Exclude people who like Defend the 2nd, age:18 - 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:The Second Amendment, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Protecting Your Gun Rights, people_who_match:interests:God, people_who_match:interests:Guns, people_who_match:interests:& American Freedom, people_who_match:interests:Preserve our right to keep and bear arms or Second Amendment Supporters, people_who_match:interests:, politics:US politics (conservative), and_must_also_match:interests:2nd Amendment, and_must_also_match:interests:National Rifle Association	0	0	https://www.facebook.com/savetheZa/	The community of 2nd Amendment supporters, guns lovers & patriots Defend The 2nd	0.0	0.00	USD	2017-04-19T07:35:54-07:00
2905		location_living_in:United States, exclude_location:united_states:California; Illinois, excluded_connections:Exclude people who like Defend the 2nd, age:18 - 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:The Second Amendment, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Protecting Your Gun Rights, people_who_match:interests:God, people_who_match:interests:Guns, people_who_match:interests:& American Freedom, people_who_match:interests:Preserve our right to keep and bear arms, people_who_match:interests:Second Amendment Supporters, and_must_also_match:interests:2nd Amendment, and_must_also_match:interests:National Rifle Association, and_must_also_match:interests:National Association for Gun Rights	0	0	https://www.facebook.com/savetheZa/	The community of 2nd Amendment supporters, guns lovers & patriots Defend The 2nd	0.0	0.00	USD	2017-04-19T07:36:54-07:00
2906		location_living_in:United States, exclude_location:united_states:California; Illinois, excluded_connections:Exclude people who like Defend the 2nd, age:18 - 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:The Second Amendment, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Protecting Your Gun Rights, people_who_match:interests:God, people_who_match:interests:Guns, people_who_match:interests:& American Freedom, people_who_match:interests:Preserve our right to keep and bear arms, people_who_match:interests:Second Amendment Supporters, and_must_also_match:interests:Right to keep and bear arms, and_must_also_match:interests:2nd Amendment, and_must_also_match:interests:National Rifle Association, and_must_also_match:interests:Gun Owners of America, and_must_also_match:interests:Second Amendment to the United States Constitution, and_must_also_match:interests:Concealed carry in the United States, and_must_also_match:interests:National Association for Gun Rights, and_must_also_match:interests:Guns & Ammo	0	0	https://www.facebook.com/savetheZa/	The community of 2nd Amendment supporters, guns lovers & patriots Defend The 2nd	0.0	0.00	USD	2017-04-19T07:37:37-07:00
2964	no image	location_living_in:united_states:Gainesville (+25 mi) Florida, age:18 — 65+, gender:Male, placements:News Feed on desktop computers, placements:News Feed on mobile devices, people_who_match:interests:Protect the Second Amendment, people_who_match:interests:Gun Owners of America, people_who_match:interests:Concealed carry, people_who_match:interests:Concealed carry in the United States, people_who_match:interests:National Association for Gun Rights, people_who_match:interests:Guns & Ammo, people_who_match:interests:Students for Concealed Carry	1149	55	https://www.facebook.com/Defend-the-2nd-692068924291672/	FREE shooting practice for our subscribers! (Hurry, only 8 packages left!) Dear subscribers, we offer you to gather together and visit shooting range in Gainesville, FL. Oh we are making an event for our subscribers. Join the event and share it with your friends. First 8 people who will message us will get a FREE 9mm Shooting Package (includes the day pass, a rental handgun, 100 rounds 9mm, 1 target, eye/ear protection if neededi. If iou want to get it, just message us Shooting practice at	16.0	1000.00	RUB	2017-02-17T07:48:04-08:00	2017-02-20T07:48:04-08:00

CREATE VIEW display_ads AS 
        select ads.id,
            case when image is not null then
                json_object("img_src", "https://raw.githubusercontent.com/umd-mith/irads/master/site/" || image, "width", 200)
            else
                "no image"
            end as img,
            json_group_array(
                json_object(
                    "label", targets.name,
                    "href", "/russian-ads/display_ads?_target="
                        || urllib_quote_plus(targets.id)
                )
            ) as targeting,
            ads.impressions, ads.clicks, ads.url, ads.text,
            cast(case
                when ads.spend_currency == "RUB" then ads.spend_amount * 0.016
                else ads.spend_amount
            end as float) as spend_usd,
            ads.spend_amount, ads.spend_currency,
            ads.created, ads.ended
        from ads
            join ad_targets on ads.id = ad_targets.ad_id
            join targets on ad_targets.target_id = targets.id
        group by ads.id
        order by ads.id