display_ads (view)

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

2 rows where an ad_target was people_who_match:interests:ForAmerica sorted by ended

This data as JSON, CSV (advanced)

id	img	targeting	impressions	clicks	url	text	spend_usd	spend_amount	spend_currency	created	ended ▼
1936		location:United States, age:13 - 65+, placements:Instagram Feed, people_who_match:interests:Proud to be an American, people_who_match:interests:Patriot Nation, people_who_match:interests:ForAmerica, people_who_match:interests:Patriotism	22567	92	https://www.instagram.oom/instotus/	We are Patriots. We fight for the traditions, for the National Symbols and for the Future. If you don't like the liberal ideas and you are not the one who supports gun control, gay marriage and black lives matter activists. If America is more than just a country for you, we want you to join us! This is a community for everyone whose heart is with America. Don't be a stranger, be a patriot! Follow us! Instagram Of The United Instotus	58.899680000000004	3681.23	RUB	2016-05-06T07:29:40-07:00
1937		location:United States, age:13 - 65+, placements:Instagram Feed, people_who_match:interests:Support our troops, people_who_match:interests:Tea Party Patriots, people_who_match:interests:Proud to be an American, people_who_match:interests:ForAmerica, people_who_match:interests:United States Armed Forces, people_who_match:interests:Veterans, people_who_match:interests:Patriotism, people_who_match:interests:The Veterans Site	22635	41	https://www.instagram.com/instotus/	This is a community for everyone whose heart is with America. Don't be a stranger - Follow @instotus Instagram Of The United Instotus	46.559839999999994	2909.99	RUB	2016-02-25T01:06:31-08:00	2016-03-24T05:00:00-07: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